﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

using VK.Comm;
using VK.Comm.TransactSql;

namespace VK.Casemeet.Articles
{
    internal static class CategoryDAL
    {
        internal static CategoryEntity GetCategoryByReader(IDataReader dr)
        {
            CategoryEntity category = null;

            category = new CategoryEntity();
            category.CategoryID = dr.GetInt32(0);
            category.Name = dr.IsDBNull(1) ? null : dr.GetString(1);
            category.Sort = dr.IsDBNull(2) ? 0 : dr.GetInt32(2);
            category.IsEnable = dr.IsDBNull(3) ? false : dr.GetBoolean(3);

            return category;
        }

        internal static int GetCategoryCount(IList<Condition> conditionArray)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectCategory = @"SELECT COUNT([CategoryID]) FROM [ArticleCategory]";

            List<DbParameter> parameterlst = new List<DbParameter>();
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectCategory, ref parameterlst);
            DbCommand SelectCategoryCommand = db.GetSqlStringCommand(SelectCategory);
            DbHelper.PackDbCommandByDbParameters(SelectCategoryCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(SelectCategoryCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static IList<CategoryEntity> GetCategoryList(int startRecordIndex, int endRecordIndex, IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<CategoryEntity> categorylst = new List<CategoryEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectCategory = @"
                SELECT [CategoryID],[Name],[Sort],[IsEnable]
	            FROM (
                    SELECT [CategoryID],[Name],[Sort],[IsEnable]
                      ,ROW_NUMBER() OVER (ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectCategory += " " + sortField + " " + sortMode + ",";
            SelectCategory += @" [CategoryID] Desc
                ) AS rowNum 
                    FROM [ArticleCategory] ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectCategory, ref parameterlst);
            SelectCategory += @"
                ) AS [ArticleCategory] 
	            WHERE rowNum BETWEEN @StartRecordIndex and @EndRecordIndex ";

            DbCommand SelectCategoryCommand = db.GetSqlStringCommand(SelectCategory);
            parameterlst.Add(DbHelper.PackInParameter(db, "@StartRecordIndex", DbType.Int32, startRecordIndex));
            parameterlst.Add(DbHelper.PackInParameter(db, "@EndRecordIndex", DbType.Int32, endRecordIndex));
            DbHelper.PackDbCommandByDbParameters(SelectCategoryCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectCategoryCommand))
            {
                while (dr.Read())
                {
                    CategoryEntity category = GetCategoryByReader(dr);

                    categorylst.Add(category);
                }
            }

            return categorylst;
        }

        internal static CategoryEntity[] GetCategoryArray()
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<CategoryEntity> categorylst = new List<CategoryEntity>();

            string SelectCategory = @"
                SELECT [CategoryID],[Name],[Sort],[IsEnable]
	            FROM [ArticleCategory]
                ORDER BY [Sort],[CategoryID] Asc";

            DbCommand SelectCategoryCommand = db.GetSqlStringCommand(SelectCategory);

            using (IDataReader dr = db.ExecuteReader(SelectCategoryCommand))
            {
                while (dr.Read())
                {
                    CategoryEntity category = GetCategoryByReader(dr);

                    categorylst.Add(category);
                }
            }

            return categorylst.ToArray();
        }

        internal static CategoryEntity GetCategory(int categoryID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            CategoryEntity category = null;

            string SelectCategory = @"
                SELECT [CategoryID],[Name],[Sort],[IsEnable]
                FROM [ArticleCategory] 
                WHERE CategoryID=@CategoryID
	           ";

            DbCommand SelectCategoryCommand = db.GetSqlStringCommand(SelectCategory);
            db.AddInParameter(SelectCategoryCommand, "@CategoryID", DbType.Int32, categoryID);

            using (IDataReader dr = db.ExecuteReader(SelectCategoryCommand))
            {
                if (dr.Read())
                {
                    category = GetCategoryByReader(dr);
                }
            }

            return category;
        }

        internal static int InsertCategory(CategoryEntity category)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string InsertCategory = @"
            DECLARE @maxSort AS INT;
            SELECT @maxSort=MAX([Sort]) FROM [ArticleCategory];
            INSERT INTO [ArticleCategory]
            ([Name],[Sort],[IsEnable])
             VALUES
            (@Name,ISNULL(@maxSort,0)+1,@IsEnable)";

            DbCommand InsertCategoryCommand = db.GetSqlStringCommand(InsertCategory);
            db.AddInParameter(InsertCategoryCommand, "@Name", DbType.String, category.Name);
            db.AddInParameter(InsertCategoryCommand, "@IsEnable", DbType.Boolean, category.IsEnable);
            result = db.ExecuteNonQuery(InsertCategoryCommand);

            return result;
        }

        internal static int UpdateCategory(CategoryEntity category)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();
            string UpdateCategory = @"
            UPDATE [ArticleCategory]
            SET [Name]=@Name,[IsEnable]=@IsEnable
             WHERE [CategoryID] = @CategoryID";

            DbCommand UpdateCategoryCommand = db.GetSqlStringCommand(UpdateCategory);
            db.AddInParameter(UpdateCategoryCommand, "@Name", DbType.String, category.Name);
            db.AddInParameter(UpdateCategoryCommand, "@IsEnable", DbType.Boolean, category.IsEnable);
            db.AddInParameter(UpdateCategoryCommand, "@CategoryID", DbType.Int32, category.CategoryID);
            result = db.ExecuteNonQuery(UpdateCategoryCommand);

            return result;
        }

        internal static int DeleteCategory(int categoryID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string DeleteCategory = @"
            DELETE FROM [Article] WHERE CategoryID=@CategoryID;
            DELETE FROM [ArticleCategory] WHERE CategoryID=@CategoryID;";

            DbCommand DeleteCategoryCommand = db.GetSqlStringCommand(WrapSql.WrapTransaction("Tran_DeleteCategory", DeleteCategory));
            db.AddInParameter(DeleteCategoryCommand, "@CategoryID", DbType.Int32, categoryID);
            result = db.ExecuteNonQuery(DeleteCategoryCommand);

            return result;
        }

        internal static int SetCategoryEnable(int categoryID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetCategory = @"UPDATE [ArticleCategory]
                   SET [IsEnable] = ([IsEnable]+1)%2
                 WHERE [CategoryID] = @CategoryID";

            DbCommand SetCategoryCommand = db.GetSqlStringCommand(SetCategory);
            db.AddInParameter(SetCategoryCommand, "@CategoryID", DbType.Int32, categoryID);
            result = db.ExecuteNonQuery(SetCategoryCommand);

            return result;
        }

        /// <summary>
        /// 向上移一位
        /// </summary>
        /// <param name="categoryID"></param>
        /// <returns>1：成功  2：已经在第一位无需移动</returns>
        internal static int SetCategoryMoveUp(int categoryID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetCategoryMoveUp = @"
/*   1：成功  2：已经在第一位无需移动   */
	DECLARE @sort AS INT, @preSort AS INT, @preCategoryID AS INT, @minSort AS INT
	DECLARE @RESULT AS INT; SET @RESULT=0;
	
	BEGIN TRANSACTION T_Article_Category_MoveUp
	
	SELECT @sort=Sort FROM ArticleCategory WHERE CategoryID=@CategoryID
	SELECT @minSort=MIN(Sort) FROM ArticleCategory
	IF @sort<>@minSort
	BEGIN
		SELECT TOP 1 @preCategoryID=CategoryID, @preSort=Sort FROM ArticleCategory WHERE Sort<@sort ORDER BY Sort DESC
		UPDATE ArticleCategory SET Sort=@preSort WHERE CategoryID=@CategoryID
		UPDATE ArticleCategory SET Sort=@sort WHERE CategoryID=@preCategoryID
		SET @RESULT=1 
	END
	ELSE
	BEGIN
		SET @RESULT=2
	END
	
	IF @@ERROR=0 
		BEGIN
		COMMIT TRANSACTION T_Article_Category_MoveUp
		END
	ELSE
		BEGIN
		ROLLBACK TRANSACTION T_Article_Category_MoveUp
		END
		
	SELECT @RESULT";

            DbCommand InsertProductCommand = db.GetSqlStringCommand(SetCategoryMoveUp);
            db.AddInParameter(InsertProductCommand, "@CategoryID", DbType.Int32, categoryID);
            result = db.ExecuteNonQuery(InsertProductCommand);

            return result;
        }

        /// <summary>
        /// 向下移一位
        /// </summary>
        /// <param name="categoryID"></param>
        /// <returns>1：成功  2：已经在最后无需移动</returns>
        internal static int SetCategoryMoveDown(int categoryID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetCategoryMoveDown = @"
    /*   1：成功  2：已经在最后无需移动   */
	DECLARE @sort AS INT, @nextSort AS INT, @nextCategoryID AS INT, @maxSort AS INT
	DECLARE @RESULT AS INT; SET @RESULT=0;
	
	BEGIN TRANSACTION T_Article_Category_MoveDown
	
	SELECT @sort=Sort FROM ArticleCategory WHERE CategoryID=@CategoryID
	SELECT @maxSort=MAX(Sort) FROM ArticleCategory 
	IF @sort<>@maxSort
	BEGIN
		SELECT TOP 1 @nextCategoryID=CategoryID, @nextSort=Sort FROM ArticleCategory WHERE Sort>@sort ORDER BY Sort
		UPDATE ArticleCategory SET Sort=@nextSort WHERE CategoryID=@CategoryID
		UPDATE ArticleCategory SET Sort=@sort WHERE CategoryID=@nextCategoryID
		SET @RESULT=1 
	END
	ELSE
	BEGIN
		SET @RESULT=2
	END
	
	IF @@ERROR=0 
		BEGIN
		COMMIT TRANSACTION T_Article_Category_MoveDown
		END
	ELSE
		BEGIN
		ROLLBACK TRANSACTION T_Article_Category_MoveDown
		END
		
	SELECT @RESULT";

            DbCommand InsertProductCommand = db.GetSqlStringCommand(SetCategoryMoveDown);
            db.AddInParameter(InsertProductCommand, "@CategoryID", DbType.Int32, categoryID);
            result = db.ExecuteNonQuery(InsertProductCommand);

            return result;
        }
    }
}
